package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import br.com.bean.NotaFaltaExameBean;

public class NotaFaltaExameDao {

	public void inserir(Connection conn, NotaFaltaExameBean value) throws SQLException {
		String sql = "INSERT INTO nota_falta_exame(nchamada, turma," +
				" disciplina, nota, falta) VALUES ( ?, ?, ?, ?, ?)";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, value.getNchamada().getNchamada());
		ps.setInt(2, value.getTurma().getId());
		ps.setInt(3, value.getDisciplina().getId());
		ps.setDouble(4, value.getNota());
		ps.setInt(5, value.getFalta());
		ps.execute();
		ps.close();

	}

	public void excluir(Connection conn, NotaFaltaExameBean value) throws Exception {

		String sql = "DELETE FROM nota_falta_exame WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.execute();
	}

	public void alterar(Connection conn, NotaFaltaExameBean value) throws Exception {

		String sql = "UPDATE nota_falta_exame SET nchamada=?, turma=? " +
				" disciplina=?, nota=?, falta=? WHERE  id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getNchamada().getNchamada());
		ps.setInt(2, value.getTurma().getId());
		ps.setInt(3, value.getDisciplina().getId());
		ps.setDouble(4, value.getNota());
		ps.setInt(5, value.getFalta());
		ps.execute();
		ps.close();
	}

	public ArrayList<NotaFaltaExameBean> getLista(Connection conn, NotaFaltaExameBean value) throws Exception{

		String sql = " SELECT nchamada, turma, disciplina, " +
				"nota, falta FROM nota_falta_exame where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<NotaFaltaExameBean> al = new ArrayList<NotaFaltaExameBean>();
		while(rs.next()){
			NotaFaltaExameBean a = new NotaFaltaExameBean();
			a.getNchamada().setNchamada(rs.getInt("nchamada"));
			a.getTurma().setId(rs.getInt("disciplina"));
			a.getDisciplina().setId(rs.getInt("disciplina"));
			a.setNota(rs.getDouble("nota"));
			a.setFalta(rs.getInt("falta"));
			al.add(a);
		}
		return al;
	}

	public ArrayList<NotaFaltaExameBean> getLista(Connection conn) throws Exception{


		String sql = " SELECT nmatricula, turma, disciplina, " +
				"nota, falta FROM nota_falta_exame";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<NotaFaltaExameBean> al = new ArrayList<NotaFaltaExameBean>();
		while(rs.next()){
			NotaFaltaExameBean a = new NotaFaltaExameBean();
			a.getNchamada().setNchamada(rs.getInt("nchamada"));
			a.getTurma().setId(rs.getInt("disciplina"));
			a.getDisciplina().setId(rs.getInt("disciplina"));
			a.setNota(rs.getDouble("nota"));
			a.setFalta(rs.getInt("falta"));
			al.add(a);
		}
		return al;
	}

}